1% completed
Problem
Table: Follow
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| followee | varchar |
| follower | varchar |
+-------------+---------+
(followee, follower) is the primary key (combination of columns with unique values) for this table.
Each row of this table indicates that the user follower follows the user followee on a social network.
There will not be a user following themself.
Problem Definition
A second-degree follower is a user who:
- follows at least one user, and
- is followed by at least one user.
Write a solution to report the second-degree users and the number of their followers.
Return the result table ordered by follower in alphabetical order.
Example
Output
Try It Yourself
Follow =
Solution
To identify second-degree followers—users who both follow at least one other user and are followed by at least one user—we can leverage SQL's self-join capabilities. The approach involves isolating users who meet both criteria and then counting their followers.
- Identify Users Who Follow Others: Determine users who are followers in the
Followtable. - Identify Users Who Are Followed: Determine users who are followees in the
Followtable. - Determine Second-Degree Followers: Find the intersection of users who follow others and are followed by others.
- Count Followers for Each Second-Degree Follower: For each second-degree follower, count the number of users who follow them.
- Order the Results: Present the final list ordered alphabetically by the follower's name.
SQL Query
SELECT f1.follower AS follower, COUNT(DISTINCT f2.follower) AS num FROM Follow f1 INNER JOIN Follow f2 ON f1.follower = f2.followee GROUP BY f1.follower ORDER BY f1.follower ASC;
Step-by-Step Approach
Step 1: Identify Users Who Follow Others
Determine all users who follow at least one other user by selecting distinct followers from the Follow table.
SQL Query:
SELECT DISTINCT follower FROM Follow;
Explanation:
SELECT DISTINCT follower:- Retrieves unique users who act as followers in the
Followtable.
- Retrieves unique users who act as followers in the
FROM Follow:- Specifies the
Followtable as the data source.
- Specifies the
Output After Step 1:
+----------+ | follower | +----------+ | Bob | | Cena | | Donald | | Edward | +----------+
Step 2: Identify Users Who Are Followed
Determine all users who are followed by at least one other user by selecting distinct followees from the Follow table.
SQL Query:
SELECT DISTINCT followee FROM Follow;
Explanation:
SELECT DISTINCT followee:- Retrieves unique users who are followed in the
Followtable.
- Retrieves unique users who are followed in the
FROM Follow:- Specifies the
Followtable as the data source.
- Specifies the
Output After Step 2:
+----------+ | followee | +----------+ | Alice | | Bob | | Donald | +----------+
Step 3: Determine Second-Degree Followers and Count Their Followers
Identify users who both follow others and are followed by others (second-degree followers) and count the number of their followers.
SQL Query:
SELECT f1.follower AS follower, COUNT(DISTINCT f2.follower) AS num FROM Follow f1 INNER JOIN Follow f2 ON f1.follower = f2.followee GROUP BY f1.follower ORDER BY f1.follower ASC;
Explanation:
FROM Follow f1 INNER JOIN Follow f2 ON f1.follower = f2.followee:- Performs a self-join on the
Followtable. f1.follower = f2.followeeensures that we're looking at users (f1.follower) who are followed by others (f2.follower).
- Performs a self-join on the
SELECT f1.follower AS follower:- Selects the user who is both a follower and a followee.
COUNT(DISTINCT f2.follower) AS num:- Counts the number of unique followers each second-degree follower has.
GROUP BY f1.follower:- Groups the results by the second-degree follower to aggregate their follower counts.
ORDER BY f1.follower ASC:- Orders the final results alphabetically by the follower's name.
Output After Step 3:
+----------+-----+ | follower | num | +----------+-----+ | Bob | 2 | | Donald | 1 | +----------+-----+
On this page
Problem
Problem Definition
Example
Output
Try It Yourself
Solution
SQL Query
Step-by-Step Approach